package App.Repository;

import App.Model.Account.CrmUser;
import App.Model.BaseEnum;
import App.Model.BaseResponse;
import App.Model.Chat.CrmChatGroup;
import App.Model.Chat.CrmChatGroupPart;
import App.Model.Chat.CrmChatStage;
import App.Model.Friend.CrmUserPart;
import App.Model.Friend.FriendContactListResponse;
import App.Model.Friend.FriendListRequest;
import App.Model.Project.*;
import App.Model.Task.*;
import io.jsonwebtoken.Header;
import org.hibernate.Session;
import org.hibernate.query.NativeQuery;
import org.hibernate.query.Query;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import java.awt.color.ICC_Profile;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by xw on 2019/1/15.
 */

@Repository
public class CommonRepository extends BaseRepository {

    public List<CrmProject> getProject(EntityManager entityManager, String projectid) {
        String _strSql = " select recid,recname,current_stage,projecttype,projectstatus,completedtime  from crm_project where recid=:projectid";
        javax.persistence.Query _query = entityManager.createNativeQuery(_strSql, CrmProject.class);
        _query.setParameter("projectid", projectid);
        List<CrmProject> _projectList = _query.getResultList();
        return _projectList;
    }


    public List<CrmProjectStage> getProjectStageList( String projectid) {

        String _strSql =
                "select s.recid,s.recname,sr.recorder,s.datastageid,s.stagetype, " +
                        " case when sr.stagestatus=2 then true else false end  as iscomplete " +
                        " from crm_project_stage s " +
                        " inner join crm_project_stage_relate sr on s.recid=sr.stageid " +
                        " where sr.recstatus=1 " +
                        " and  sr.projectid=:projectid" +
                        " order by sr.recorder asc ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("projectid", projectid);
        List<CrmProjectStage> _stageList =getEntitys(_strSql,_params,CrmProjectStage.class);
        return _stageList;
    }

    public CrmChatGroup getChatGroup(String objectid) {
        CrmChatGroup _group = new CrmChatGroup();
        String _strSql = " select color,recid,recname,objectid,chattype,chatstageid,prefixname,stageid,grouptype,relatedid,'' as taskbegintime,isactive,createtime  " +
                " from crm_chat_group " +
                " where objectid=:objectid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("objectid", objectid);
        List<CrmChatGroup> _list =getEntitys(_strSql,_params,CrmChatGroup.class);
        if (_list != null && _list.size() > 0) {
            _group = _list.get(0);
        }
        return _group;
    }

    public int insertProjectFile(EntityManager entityManager, String projectid, int stageid, String recid, ProjectFile _file, int userid) {
        int _result = 0;

        String _strSql = " insert into crm_project_file(recid,projectid,stageid,folderid,uploadfileid,filename,filetype,reccreator) " +
                "values(:recid,:projectid,:stageid,:folderid,:uploadfileid,:filename,:filetype,:reccreator)";

        javax.persistence.Query _query = entityManager.createNativeQuery(_strSql);
        _query.setParameter("recid", recid);
        _query.setParameter("projectid", projectid);
        _query.setParameter("stageid", stageid);
        _query.setParameter("folderid", _file.getFolderid());
        _query.setParameter("uploadfileid", _file.getUploadfileid());
        _query.setParameter("filename", _file.getFilename());
        _query.setParameter("filetype", _file.getFiletype());
        _query.setParameter("reccreator", userid);
        _result = _query.executeUpdate();

        return _result;
    }

    public List<CrmProjectMember> getProjectMember(String projectid, int userid) {

        //团队成员
        String _strSql = " select recid,stageid,groupid,userid,u.realname as username,ismanager,grouptype,innergrouptype,relatedid,ishost,projectid " +
                " from crm_project_member m " +
                " inner join crm_user u on m.userid=u.id " +
                " where m.recstatus=1 " +
                " and  projectid=:projectid" +
                " order by m.createtime asc ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("projectid", projectid);
        List<CrmProjectMember> _memberList = getEntitys(_strSql, _params, CrmProjectMember.class);
        return _memberList;
    }


    public List<CrmProjectMember> getProjectStagetMember(String projectid,int stageid, int userid) {

        //团队成员
        String _strSql = " select recid,stageid,groupid,userid,u.realname as username,ismanager,grouptype,innergrouptype,relatedid,ishost,projectid " +
                " from crm_project_member m " +
                " inner join crm_user u on m.userid=u.id " +
                " where m.recstatus=1 " +
                " and  projectid=:projectid " +
                " and  m.stageid=:stageid " +
                " order by m.createtime asc ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("projectid", projectid);
        _params.put("stageid", stageid);
        List<CrmProjectMember> _memberList = getEntitys(_strSql, _params, CrmProjectMember.class);
        return _memberList;
    }


    public CrmUser getUserInfo(int userid) {

        String strSql = "select * from crm_user where id=:userid";
        Map<String, Object> _params = new HashMap<>();
        _params.put("userid", userid);
        List<CrmUser> _list = getEntitys(strSql, _params, CrmUser.class);
        if (_list != null && _list.size() > 0) {
            return _list.get(0);
        } else {
            return null;
        }
    }


    public List<CrmChatGroupPart> getProjectChatGroup(String objectid) {

        String _strSql = "select recid,recname,objectid,stageid,chattype,grouptype,relatedid,prefixname,chatstageid, topicname " +
                " from crm_chat_group g " +
                " INNER JOIN ( SELECT recid AS projectid, recname AS topicname FROM crm_project WHERE recstatus = 1 ) p ON g.objectid = p.projectid  " +
                " where recstatus=1 " +
                " and  chattype=0 " +
                " and  objectid=:objectid  ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("objectid", objectid);
        List<CrmChatGroupPart> _list = getEntitys(_strSql, _params, CrmChatGroupPart.class);
        return _list;
    }

    public List<CrmChatGroupPart> getTaskChatGroup(String objectid) {

        String _strSql = "select recid,recname,objectid,stageid,chattype,grouptype,relatedid,prefixname,chatstageid, topicname " +
                " from crm_chat_group g" +
                " INNER JOIN ( SELECT recid AS taskid, content AS topicname FROM crm_task WHERE recstatus = 1 ) p ON g.objectid = p.taskid " +
                " where recstatus=1 " +
                " and  chattype=1 " +
                " and  objectid=:objectid  ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("objectid", objectid);
        List<CrmChatGroupPart> _list = getEntitys(_strSql, _params, CrmChatGroupPart.class);
        return _list;
    }


    public boolean isRequestMemberExists(List<ProjectMember> _requestMembers, List<CrmProjectMember> _savedMembers) {
        boolean _isMeberChanged = false;
        for (ProjectMember m : _requestMembers) {
            long _theMemberCount = _savedMembers.stream().filter(x -> x.getUserid() == m.getUserid()).count();
            if (_theMemberCount == 0) {
                _isMeberChanged = true;
                break;
            }
        }
        return _isMeberChanged;
    }


    public boolean isSavedMemberExists(List<CrmProjectMember> _savedMembers, List<ProjectMember> _requestMembers) {
        boolean _isMeberChanged = false;
        for (CrmProjectMember m : _savedMembers) {
            long _theMemberCount = _requestMembers.stream().filter(x -> x.getUserid() == m.getUserid()).count();
            if (_theMemberCount == 0) {
                _isMeberChanged = true;
                break;
            }
        }
        return _isMeberChanged;
    }


    public List<CrmChatStage> getChatStages(int projectType, String groupid, int stageid, String titleid) {

        String _stageSql = " select s.recid,s.recname,s.stagetype,coalesce(h.chatstageid,0) branchstatus " +
                " from crm_chat_stage s " +
                " left join (select * from crm_chat_branch_stage_history where groupid=:groupid) h on s.recid=h.chatstageid " +
                " where s.recstatus=1 " +
                " and s.projecttype=:projecttype" +
                " and s.stageid=:stageid " +
                " and s.titleid=:titleid " +
                " order by s.recorder asc ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("groupid", groupid);
        _params.put("projecttype", projectType);
        _params.put("stageid", stageid);
        _params.put("titleid", titleid);
        List<CrmChatStage> _stageList = getEntitys(_stageSql, _params, CrmChatStage.class);
        return _stageList;

    }

    //更新聊天组的状态
    public int updateChatGroupStatus(String recid, int recstatus) {
        String _strSql = " update crm_chat_group set recstatus=:recstatus where recid=:recid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", recid);
        _params.put("recstatus", recstatus);
        return updateEntity(_strSql, _params);
    }

    public List<CrmProject> getProject(String projectid) {
        String _strSql = " select recid,recname,current_stage,projecttype,projectstatus,completedtime  " +
                " from crm_project where recid=:projectid ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("projectid", projectid);
        List<CrmProject> _projectList = getEntitys(_strSql,_params,CrmProject.class);
        return _projectList;
    }

    public List<CrmProjectStage> getProjectStage(int stageid) {
        String _strSql = " select *,false as iscomplete from crm_project_stage where recid=:recid ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("recid", stageid);
        List<CrmProjectStage> _stages = getEntitys(_strSql,_params,CrmProjectStage.class);
        return _stages;
    }

    public List<CrmTask> getTaskByProjectStage(String projectid, int stageid) {

        String _strSql = " select t.recid, t.recname, t.begintime, t.content, t.contenttype, t.address_name, " +
                "t.address_location, t.taskstatus, " +
                "t.completetime, t.recstatus, t.createtime, t.reccreator  from crm_project_task pt " +
                " inner join crm_task t on pt.taskid=t.recid " +
                " where pt.projectid=:projectid " +
                " and pt.stageid=:stageid " +
                " and pt.recstatus=1 ";

        Map<String, Object> _params = new HashMap<>();
        _params.put("projectid", projectid);
        _params.put("stageid", stageid);
        return getEntitys(_strSql,_params,CrmTask.class);

    }

    public List<CrmTaskMember> getTaskMembers(String taskid) {

        String _strSql = " select m.recid,m.groupid,m.userid,m.roleid,m.ismanager,m.ishost," +
                " coalesce(u.realname,'') as username,m.reccreator,m.recorder " +
                " from  crm_task_member m" +
                " inner join crm_user u on m.userid=u.id " +
                " where m.recstatus=1 " +
                " and  taskid=:taskid ";

        javax.persistence.Query _query = getEntityManager().createNativeQuery(_strSql, CrmTaskMember.class);
        Map<String, Object> _params = new HashMap<>();
        _params.put("taskid", taskid);
        return  getEntitys(_strSql,_params, CrmTaskMember.class);
    }

    public List<CrmProjectStageRelate> getProjectStageRelate(String projectid) {

        String _strSql = " select recid,stageid,stagestatus,completedtime,createtime,projectid " +
                " from crm_project_stage_relate " +
                " where recstatus=1 " +
                " and  projectid=:projectid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("projectid", projectid);
        List<CrmProjectStageRelate> _stageRelates = getEntitys(_strSql, _params, CrmProjectStageRelate.class);
        return _stageRelates;
    }

    public CrmUserPart getUserInfoALL( int userid) {;
            String _strSql = " select id,username,headimage,realname,gender,title,company,address,signature  " +
                    " from crm_user " +
                    " where id=:userid " ;

            Map<String, Object> _params = new HashMap<>();
            _params.put("userid", userid);
            List<CrmUserPart> _tmpFriends = getEntitys(_strSql, _params, CrmUserPart.class);

            if(_tmpFriends!=null&&_tmpFriends.size()>0){
                return  _tmpFriends.get(0);
            }else{
                return  null;
            }
    }

    public List<CrmTaskFile> getTaskFiles(String taskid) {

        String _strSql = " select t.recid,t.fileid,t.filename,t.file_suffix,coalesce(u.showfilename,'') as showfilename,t.reccreator,t.sourcefilename,t.isviewed,''as uploader, u.createtime as createtime " +
                "  from crm_task_file t " +
                " left join crm_upload_file u on t.fileid=u.fileid" +
                " where t.recstatus=1 " +
                " and  t.taskid=:taskid " +
                " order by convert(filename using gbk)  asc ";

        javax.persistence.Query _query = getEntityManager().createNativeQuery(_strSql, CrmTaskFile.class);
        _query.setParameter("taskid", taskid);
        return _query.getResultList();
    }

    public int getFileViewedCount(String  filerecid, int userid) {
        String _strSql = " select count(1) from  crm_view_file_history where filerecid=:filerecid and viewerid=:viewerid ";
        Map<String, Object> _params = new HashMap<>();
        _params.put("filerecid", filerecid);
        _params.put("viewerid", userid);
        return getEntityCount(_strSql, _params);
    }

    public int setTaskChatGroupActive(String groupid, boolean isactive) throws Exception {
        int _result = 0;
        String _strSql = " update crm_chat_group set isactive=:isactive " +
                " where recid=:recid ";
        Map<String, Object> _param = new HashMap<>();
        _param.put("recid", groupid);
        _param.put("isactive", isactive);
        _result = updateEntity(_strSql, _param);
        return _result;
    }

    public List<CrmChatGroup> getProjectTaskChatGroups(String projectid)  {

        String _strSql = " select g.recid,g.recname,g.objectid,g.chattype,g.chatstageid,g.prefixname," +
                " g.stageid,g.grouptype,g.relatedid,''as taskbegintime,g.isactive " +
                "    from crm_project_task pt" +
                "    inner join crm_chat_group g on pt.taskid=g.objectid" +
                "    where pt.recstatus=1 " +
                "    and pt.projectid=:projectid ";
        Map<String, Object> _param = new HashMap<>();
        _param.put("projectid", projectid);

        return getEntitys(_strSql, _param,CrmChatGroup.class);
    }



    public int setTaskChatGroupActive2(String groupid, boolean isactive) {
        int _result = 0;
        String _strSql = " update crm_chat_group set isactive=:isactive " +
                " where recid=:recid ";
        Map<String, Object> _param = new HashMap<>();
        _param.put("recid", groupid);
        _param.put("isactive", isactive);
        _result = updateEntity(_strSql, _param);
        return _result;
    }

    public List<CrmUser> getUserListByIds(String userIds) {
        String strSql = "select * from crm_user where FIND_IN_SET(id, :userid)";
        List<CrmUser> _list = new ArrayList<>();
        Map<String, Object> _param = new HashMap<>();
        _param.put("userid", userIds);
        try {
            _list = getEntitys(strSql, _param, CrmUser.class);

        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return _list;
    }
}




